package vivek.project.pocketmoney.admin.model
{
	import flash.data.*;
	import flash.errors.*;
	import flash.events.Event;
	import flash.filesystem.File;
	import flash.filesystem.FileMode;
	import flash.filesystem.FileStream;
	import mx.collections.ArrayCollection;
	import mx.controls.Alert;
	import mx.core.UIComponent;
	import mx.utils.StringUtil;
	import vivek.project.pocketmoney.admin.scripts.AdminSQLScript;
	import vivek.project.pocketmoney.common.CommonData;
	import vivek.project.pocketmoney.common.utils.CommonUtils;
	import vivek.project.pocketmoney.expenses.model.ExpenseDataModel;
	import vivek.project.pocketmoney.expenses.scripts.ExpenseSQLScript;

	public class AdminDataModel extends UIComponent
	{
		private static var instance:AdminDataModel;

		public static function getAccountNameById(accountId:int):String
		{
			var accObj:Object=new Object();
			for each (accObj in AdminDataCache.accountDataAll)
			{
				if (accObj.am_accountid == accountId)
					return accObj.am_accountname;
			}
			return "";
		}

		public function loadControlData():void
		{
			var fileInfo:String=CommonUtils.loadFileFromStorage("controldata.txt", ",");
			if (fileInfo == null)
			{
				var controlFile:File=new File(File.applicationStorageDirectory.nativePath + ("/controldata.txt"));
				var fs:FileStream=new FileStream();
				fs.open(controlFile, FileMode.WRITE);
				fs.writeUTF("bullion_value,1890");
				fs.close();
				fileInfo=CommonUtils.loadFileFromStorage("controldata.txt", ",");
			}
			var arrayList:Array=new Array();
			var parts:Array=fileInfo.split("\n");
			for (var i:int=0; i < parts.length; i++)
			{
				var obj:Object=new Object();
				var splitInfo:Array=parts[i].split(",");
				obj.property=StringUtil.trim(splitInfo[0]);
				obj.value=StringUtil.trim(splitInfo[1]);
				arrayList.push(obj);
			}
			AdminDataCache.controlData.source=arrayList;
			dispatchEvent(new Event("CONTROL_DATA_CHANGED"));
		}

		public static function getCurrencyIdByName(cuName:String):int
		{
			var obj:Object=new Object();
			for each (obj in AdminDataCache.currencyData)
			{
				if (obj.cu_name == cuName)
					return obj.cu_id;
			}
			return 0;
		}

		public static function getInstance():AdminDataModel
		{
			if (!instance)
			{
				/*instance = ExpenseDataModel(
				 Singleton.getInstance("vivek.project.pocketmoney.expenses.model::ExpenseDataModel"));*/
				instance=new AdminDataModel();
			}
			return instance;
		}

		public function AdminDataModel()
		{
			loadFirstTimeData();
			//getDefaultUser();
			setUser();
			loadUserAccounts();
			//setUser();
			//addEventListener("USERCHANGED", loadDataOnEvent);
		}

		public function setUserID(id:int):void
		{
			var connection:SQLConnection=CommonData.getConnection();
			var sql:SQLStatement=new SQLStatement();
			sql.sqlConnection=connection;
			connection.begin();
			sql.text="delete from selecteduser";
			sql.execute();
			sql.text="insert into selecteduser (userid) values (:USERID)";
			if (id == 0)
			{
				for each (var object:Object in AdminDataCache.userData)
				{
					sql.parameters[":USERID"]=object.um_userid;
					sql.execute();
				}
			}
			else
			{
				sql.parameters[":USERID"]=id;
				sql.execute();
			}
			AdminDataCache.ACCOUNT_IN_USE=id;
			connection.commit();
			loadUserAccounts();
			dispatchEvent(new Event("USERCHANGED"));
		}
		private var _sqlConnection:SQLConnection=CommonData.getConnection();

		public function addAccount(obj:Object):int
		{
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				_statement.sqlConnection=_sqlConnection;
				_statement.text=AdminSQLScript.ADD_NEW_ACCOUNT;
				_statement.parameters[":am_accountName"]=obj.accountName;
				_statement.parameters[":am_description"]=obj.description;
				_statement.parameters[":am_currencyid"]=obj.currency;
				_statement.parameters[":am_accountType"]=obj.accountType;
				_statement.parameters[":am_color"]=obj.color;
				_statement.parameters[":am_minBalance"]=obj.minimum;
				_statement.parameters[":USERID"]=AdminDataCache.ACCOUNT_IN_USE;
				_statement.execute();
				dispatchEvent(new Event("ACCOUNTADDED"));
				loadDataOnEvent(null);
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
			}
			return 1;
		}

		public function addChannel(obj:Object):int
		{
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				_statement.sqlConnection=_sqlConnection;
				_statement.text=AdminSQLScript.ADD_NEW_CHANNEL;
				_statement.parameters[":cm_channelname"]=obj.channelName;
				_statement.parameters[":cm_channeldesc"]=obj.channelDescription;
				_statement.parameters[":USERID"]=AdminDataCache.ACCOUNT_IN_USE;
				_statement.execute();
				loadDataOnEvent(null);
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
			}
			return 1;
		}

		public function addCurrency(obj:Object):int
		{
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				_statement.sqlConnection=_sqlConnection;
				_statement.text=AdminSQLScript.ADD_NEW_CURRENCY;
				_statement.parameters[":cu_currencyName"]=obj.currencyName;
				_statement.parameters[":cu_exRate"]=obj.exRate;
				_statement.parameters[":cu_symbol"]=obj.currencySymbol;
				_statement.execute();
				loadDataOnEvent(null);
			}
			catch (sqlError:SQLError)
			{
				return 999;
			}
			return 1;
		}

		public function addUser(obj:Object):int
		{
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				_statement.sqlConnection=_sqlConnection;
				_statement.text=AdminSQLScript.ADD_NEW_USER;
				_statement.parameters[":UM_USERNAME"]=obj.userName;
				_statement.execute();
				loadDataOnEvent(null);
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
			}
			return 1;
		}

		public function backupDatabase():void
		{
			var tableSql:String="SELECT tbl_name FROM main.sqlite_master ";
			var conn:SQLConnection=CommonData.getConnection();
			var statement:SQLStatement=new SQLStatement();
			var tables:Array=new Array();
			statement.text=tableSql;
			statement.sqlConnection=conn;
			statement.execute();
			tables=statement.getResult().data;
			for each (var obj:Object in tables)
			{
				trace(obj.tbl_name);
			}
		}

		public function deleteAccount(obj:Object):int
		{
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				_statement.sqlConnection=_sqlConnection;
				_statement.text=AdminSQLScript.DELETE_ACCOUNT;
				_statement.parameters[":am_accountid"]=obj.accountid;
				_statement.execute();
				loadDataOnEvent(null);
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
			}
			return 1;
		}

		public function getAccount(obj:Object):Object
		{
			var obj1:Object=new Object();
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				_statement.sqlConnection=_sqlConnection;
				_statement.text=AdminSQLScript.SELECT_ACCOUNT_DATA;
				_statement.parameters[":am_accountname"]=obj.am_accountname;
				_statement.parameters[":USERID"]=AdminDataCache.ACCOUNT_IN_USE;
				_statement.execute();
				var result:SQLResult=_statement.getResult();
				// access the result data
				if (result.data != null)
				{
					var numRows:int=result.data.length;
					for (var i:int=0; i < numRows; i++)
					{
						var output:Object=result.data[i];
						obj1.am_accountname=output.am_accountname;
						obj1.am_description=output.am_description;
						obj1.am_minimum=output.am_minimum;
						obj1.am_color=output.am_color;
						obj1.am_accounttype=output.at_desc;
						obj1.am_userid=output.am_userid;
						obj1.am_currencyid=output.cu_name;
					}
				}
				loadDataOnEvent(null);
			}
			catch (sqlError:SQLError)
			{
				//Alert.show(sqlError.details);
			}
			return obj1;
		}

		public static function getAccountDetailsByName(accountName:String):Object
		{
			var accObj:Object=new Object();
			for each (accObj in AdminDataCache.accountDataAll)
			{
				if (accObj.am_accountname == accountName)
				{
					return accObj;
				}
			}
			return null;
		}

		public function getAccountForId(id:int):Object
		{
			for each (var o:Object in AdminDataCache.accountDataAll)
			{
				if (o.am_accountid == id)
					return o;
			}
			return "";
		}

		public function getAccountIdByName(accountname:String):int
		{
			var accObj:Object=new Object();
			for each (accObj in AdminDataCache.accountData)
			{
				if (accObj.am_accountname == accountname)
					return accObj.am_accountid;
			}
			return 0;
		}

		public function getAccountNameForId(id:int):String
		{
			for each (var o:Object in AdminDataCache.accountData)
			{
				if (o.am_accountid == id)
					return o.am_accountname;
			}
			return "";
		}

		public function getUserNameForId(id:int):String
		{
			for each (var o:Object in AdminDataCache.userData)
			{
				if (o.um_userid == id)
					return o.um_username;
			}
			return "";
		}

		public function getAccountTypeId(typeDesc:String):int
		{
			var obj:Object=new Object();
			for each (obj in AdminDataCache.accountType)
			{
				if (obj.at_desc == typeDesc)
					return obj.at_id;
			}
			return 0;
		}

		public function getAccountsForType(id:int):ArrayCollection
		{
			var obj:Object=new Object();
			var accounts:ArrayCollection=new ArrayCollection();
			for each (obj in AdminDataCache.accountData)
			{
				if (obj.am_accounttype == id)
					accounts.addItem(obj);
			}
			return accounts;
		}

		public function getChannelForId(id:int):Object
		{
			for each (var obj:Object in AdminDataCache.channelData)
			{
				if (obj.cm_channelid == id)
					return obj;
			}
			return null;
		}

		public function getCurrencyDetailsByName(cuName:String):Object
		{
			var obj:Object=new Object();
			for each (obj in AdminDataCache.currencyData)
			{
				if (obj.cu_name.toUpperCase() == cuName.toUpperCase())
				{
					return obj;
				}
			}
			return obj;
		}

		public function getCurrencyDetailsForAccount(accountid:int):Object
		{
			var obj:Object=new Object();
			var accObj:Object=new Object();
			accObj=getAccountForId(accountid);
			for each (obj in AdminDataCache.currencyData)
			{
				if (obj.cu_name == accObj.cu_name)
					return obj;
			}
			return obj;
		}

		public function setDefaultUser(id:int):Object
		{
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				_statement.sqlConnection=_sqlConnection;
				_statement.text=AdminSQLScript.SET_USER_DEFAULT;
				_statement.parameters[":UM_USERID"]=id;
				_statement.execute();
				_statement.text=AdminSQLScript.SET_USER_NODEFAULT;
				_statement.parameters[":UM_USERID"]=id;
				_statement.execute();
				dispatchEvent(new Event("CoreDataChanged"));
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
			}
			return 1;
		}

		public function setUser():Object
		{
			var obj:Object=new Object();
			for each (obj in AdminDataCache.userData)
			{
				if (obj.um_status == 1)
				{
					AdminDataCache.ACCOUNT_IN_USE=obj.um_userid;
					AdminDataCache.ACCOUNT_IN_USE_OBJECT=obj;
					setUserID(obj.um_userid);
					return obj;
				}
			}
			return null; //return AdminDataCache.userData.getItemAt(0);
		}

		public function getDefaultUser():Object
		{
			var obj:Object=new Object();
			for each (obj in AdminDataCache.userData)
			{
				if (obj.um_status == 1)
				{
					return obj;
				}
			}
			return null;
		}

		public function tranferAccountOwnership(account:int, currentUser:int, newUser:int):int
		{
			var queries:Array=new Array();
			queries[0]="update expensemanager set em_userid=" + newUser + " WHERE EM_accountid=" + account;
			queries[1]="update MUTUALFUNDTRANSACTION set mt_userid=" + newUser + " WHERE MT_account=" + account;
			queries[2]="update ACCOUNTMANAGER set Am_userid=" + newUser + " WHERE AM_USERID=" + currentUser + "  and am_accountid=" + account;
			queries[3]="update MUTUALFUNDRECURSSION set mr_userid=" + newUser + " WHERE Mr_account=" + account;
			queries[4]="update recurringexpensemanager set rm_userid=" + newUser + " WHERE rm_fromaccount=" + account;
			queries[5]="update StockTransaction  set st_userid=" + newUser + " WHERE st_accountid=" + account;
			queries[6]="update MUTUALFUNDTRANSACTION set mt_userid=" + newUser + " WHERE MT_channel=" + account;
			queries[7]="update StockTransaction  set st_userid=" + newUser + " WHERE st_channelid=" + account;
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				_statement.sqlConnection=_sqlConnection;
				_sqlConnection.begin();
				for (var i:int=0; i < queries.length; i++)
				{
					_statement.text=queries[i];
					_statement.execute();
				}
				_sqlConnection.commit();
				loadDataOnEvent(null);
			}
			catch (sqlError:SQLError)
			{
				_sqlConnection.rollback();
				Alert.show(sqlError.details);
			}
			return 1;
		}

		public function updateAccount(obj:Object):int
		{
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				_statement.sqlConnection=_sqlConnection;
				_statement.text=AdminSQLScript.UPDATE_ACCOUNT;
				_statement.parameters[":am_accountName"]=obj.accountName;
				_statement.parameters[":am_description"]=obj.description;
				_statement.parameters[":am_currencyid"]=obj.currency;
				_statement.parameters[":am_accounttype"]=obj.accountType;
				_statement.parameters[":am_color"]=obj.color;
				_statement.parameters[":am_minimum"]=obj.minimum;
				_statement.parameters[":am_accountid"]=obj.accountId;
				_statement.execute();
				loadDataOnEvent(null);
				dispatchEvent(new Event("CoreDataChanged"));
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
			}
			return 1;
		}

		public function updateCurrency(obj:Object):int
		{
			try
			{
				var _statement:SQLStatement=new SQLStatement();
				_statement.sqlConnection=_sqlConnection;
				_statement.text=AdminSQLScript.UPDATE_CURRENCY;
				_statement.parameters[":cu_currencyName"]=obj.currencyName;
				_statement.parameters[":cu_exRate"]=obj.exRate;
				_statement.parameters[":cu_symbol"]=obj.currencySymbol;
				_statement.parameters[":cu_id"]=obj.currencyId;
				_statement.execute();
				loadDataOnEvent(null);
			}
			catch (sqlError:SQLError)
			{
				Alert.show(sqlError.details);
			}
			return 1;
		}

		private function loadFirstTimeData():void
		{
			var _statement:SQLStatement=new SQLStatement();
			//AdminDataCache.accountData = new ArrayCollection(_statement.getResult().data);
			_statement.sqlConnection=_sqlConnection;
			_statement.text=AdminSQLScript.SELECT_CURRENCY_ALL_DETAILS;
			_statement.execute();
			AdminDataCache.currencyData=new ArrayCollection(_statement.getResult().data);
			_statement.text=AdminSQLScript.SELECT_ACCOUNT_TYPE_ALL;
			_statement.execute();
			AdminDataCache.accountType=new ArrayCollection(_statement.getResult().data);
			_statement.text=AdminSQLScript.SELECT_CHANNEL_ALL;
			_statement.execute();
			AdminDataCache.channelData=new ArrayCollection(_statement.getResult().data);
			_statement.text=AdminSQLScript.SELECT_ACCOUNT_ALL;
			_statement.execute();
			AdminDataCache.accountDataAll.source=_statement.getResult().data;
			_statement.text=AdminSQLScript.SELECT_USER_ALL;
			_statement.execute();
			AdminDataCache.userData.source=_statement.getResult().data;
			loadControlData();
		}

		private function loadUserAccounts():void
		{
			var _statement:SQLStatement=new SQLStatement();
			_statement.sqlConnection=CommonData.getConnection();
			_statement.text=AdminSQLScript.SELECT_USER_ALL;
			_statement.execute();
			AdminDataCache.userData.source=_statement.getResult().data;
			_statement.text=AdminSQLScript.SELECT_ACCOUNT_ALL_USER;
			_statement.parameters[":USERID"]=AdminDataCache.ACCOUNT_IN_USE;
			_statement.execute();
			AdminDataCache.accountData.source=_statement.getResult().data;
			refreshCashAccountsAllUsers();
			refreshCashAccount();
			refreshDematAccount();
			refreshFixedIncomeAccount();
			//AdminDataCache.accountData.source=new Array();//.removeAll();
			//ArrayUtils.copyAllElements(new ArrayCollection(_statement.getResult().data),AdminDataCache.channelData); 
		}

		private function loadDataOnEvent(event:Event):void
		{
			loadUserAccounts();
		}

		private function refreshCashAccount():void
		{
			var temp:Array=new Array();
			AdminDataCache.cashAccounts.source=new Array();
			for each (var o:Object in AdminDataCache.accountData)
				//do not show investment account
				if (o.am_accounttype == 1 || o.am_accounttype == 2 || o.am_accounttype == 4 || o.am_accounttype == 11)
				{
					temp.push(o);
				}
			AdminDataCache.cashAccounts=new ArrayCollection(temp);
		}

		private function refreshCashAccountsAllUsers():void
		{
			var temp:Array=new Array();
			AdminDataCache.cashAccounts.source=new Array();
			for each (var o:Object in AdminDataCache.accountDataAll)
				//do not show investment account
				if (o.am_accounttype == 1 || o.am_accounttype == 2 || o.am_accounttype == 4 || o.am_accounttype == 11)
				{
					temp.push(o);
				}
			AdminDataCache.cashAccountsAllUsers=new ArrayCollection(temp);
		}

		private function refreshDematAccount():void
		{
			var temp:Array=new Array();
			//do not show investment account
			for each (var o:Object in AdminDataCache.accountData)
				if (o.am_accounttype == 3)
				{
					temp.push(o);
				}
			AdminDataCache.dematAccounts=new ArrayCollection(temp);
		}

		private function refreshFixedIncomeAccount():void
		{
			var temp:Array=new Array();
			for each (var o:Object in AdminDataCache.accountData)
				//do not show investment account
				if (o.am_accounttype == 6 || o.am_accounttype == 11 || o.am_accounttype == 12 || o.am_accounttype == 13 || o.am_accounttype == 14 || o.am_accounttype == 15 || o.am_accounttype == 16)
				{
					temp.push(o);
				}
			AdminDataCache.fixedIncomeAccounts=new ArrayCollection(temp);
		}
	}
}

